# -*- coding: utf-8 -*-
from datetime import datetime
import mysql.connector
from mysql.connector import Error
from ai_client import extract_main_content, score_paper_title, score_workload, score_refSurvey, \
    score_contentOrganization, score_logicStructure, score_others

DB_HOST = "127.0.0.1"
DB_USER = "root"
DB_PASSWORD = "root"
DB_NAME = "school_paper"

def get_connection():
    try:
        connection = mysql.connector.connect(
            host=DB_HOST,
            database=DB_NAME,
            user=DB_USER,
            password=DB_PASSWORD,
            port=3305,
            # 建议设置连接超时
            connection_timeout=10  # 10秒超时
        )
        return connection
    except Error as e:
        print(f"数据库连接失败: {e}")
        return None

def validate_student(account: str, name: str, major_id: int, teacher: int, teacher2: int) -> bool:
    conn, cursor = None, None
    try:
        conn = get_connection()
        cursor = conn.cursor(dictionary=True)
        cursor.callproc("select_student", (account, name, major_id, teacher, teacher2))
        for result in cursor.stored_results():
            row = result.fetchone()
            if row:
                return row["account"]
        return False
    except Error as e:
        print("校验学生失败:", e)
        return False
    finally:
        if cursor:
            cursor.close()
        if conn:
            conn.close()
def get_majorId(major_name:str) -> int:
    conn, cursor = None, None
    try:
        conn = get_connection()
        cursor = conn.cursor(dictionary=True)
        cursor.callproc("select_majorId", (f"%{major_name}%",))
        for result in cursor.stored_results():
            row = result.fetchone()
            if row:
                return row["major_id"]
        return False
    except Error as e:
        print("匹配专业失败", e)
        return False
    finally:
        if cursor:
            cursor.close()
        if conn:
            conn.close()
def get_typeId(template_type_name:str) -> int:
    conn, cursor = None, None
    try:
        conn = get_connection()
        cursor = conn.cursor(dictionary=True)
        cursor.callproc("select_typeId", (template_type_name,))
        for result in cursor.stored_results():
            row = result.fetchone();
            if row:
                return row["template_type_id"]
        return False
    except Error as e:
        print("匹配模板失败", e)
        return False
    finally:
        if cursor:
            cursor.close()
        if conn:
            conn.close()
def get_userId(name:str) -> int:
    conn, cursor = None, None
    try:
        if name:
            conn = get_connection()
            cursor = conn.cursor(dictionary=True)
            cursor.callproc("select_userId", (name,))
            for result in cursor.stored_results():
                row = result.fetchone()
                if row:
                    return row["id"]
            return False
        else:
            return None
    except Error as e:
        print("匹配用户失败", e)
        return False
    finally:
        if cursor:
            cursor.close()
        if conn:
            conn.close()
def get_templateId(major_id:int, year:int, type_id:int) -> int:
    conn, cursor = None, None
    try:
        conn = get_connection()
        cursor = conn.cursor(dictionary=True)
        cursor.callproc("select_templateId", (major_id, year, type_id))
        for result in cursor.stored_results():
            row = result.fetchone()
            if row:
                return row["template_id"]
        return False
    except Error as e:
        print("匹配模板失败", e)
        return False
    finally:
        if cursor:
            cursor.close()
        if conn:
            conn.close()
def get_templatePara(template_id:int) -> dict:
    conn, cursor = None, None
    try:
        conn = get_connection()
        cursor = conn.cursor(dictionary=True)
        cursor.callproc("select_templatePara", (template_id,))
        for result in cursor.stored_results():
            row = result.fetchone()
            if row:
                return row["content_count"], row["passage_count"], row["refer_count"], row["five_year"], row["three_year"], row["english_refer"]
        return False
    except Error as e:
        print("参数查找失败", e)
        return False
    finally:
        if cursor:
            cursor.close()
        if conn:
            conn.close()
def get_paperPara(paper_id:int) -> dict:
    conn, cursor = None, None
    try:
        conn = get_connection()
        cursor = conn.cursor(dictionary=True)
        cursor.callproc("select_paperPara", (paper_id,))
        for result in cursor.stored_results():
            row = result.fetchone()
            if row:
                return row["content_count"], row["passage_count"], row["refer_count"], row["five_year"], row["three_year"], row["english_refer"]
    except Error as e:
        print("参数查找失败", e)
        return False
    finally:
        if cursor:
            cursor.close()
        if conn:
            conn.close()
def save_paper_to_db(result: dict, upload_id: int, save_path: str) -> int:
    conn, cursor = None, None
    try:
        conn = get_connection()
        cursor = conn.cursor()

        info = result["info"]
        major_id = get_majorId(info.get("major"))
        type_id = get_typeId(info.get("type"))
        student_id = get_userId(info.get("author"))
        teacher = get_userId(info.get("teacher1"))
        teacher2 = None
        if info.get("teacher2") != "":
            teacher2 = get_userId(info.get("teacher2"))
        template_id = get_templateId(major_id, int(info.get("publishedTime")[:4]), type_id)
        text = ""
        url = save_path
        paper_chapter = []
        template_chapter = []
        #存论文元数据
        cursor.callproc("add_split_paper_metadata", (info.get("title"), info.get("publishedTime"), url, template_id, type_id, student_id, upload_id, major_id, teacher, teacher2))
        cursor.execute("SELECT LAST_INSERT_ID()")
        paper_id = cursor.fetchone()[0]
        #存摘要
        cursor.callproc("add_split_abstract", (info.get("abstractCh"),info.get("abstractEn"), paper_id))
        #存关键词
        cursor.callproc("add_split_keywords", (info.get("keywordsCh"), info.get("keywordsEn"), paper_id))
        #存正文段落
        for para in result.get("paragraph", []):
            cursor.callproc("add_split_chapter", (para.get("title"), para.get("content"), para.get("order"), paper_id))
            text += para.get("content")
            paper_chapter.append(para.get("title"))
        #存参考文献
        for ref in result["reference"]:
            ref = ref.get("info",{})
            result = cursor.callproc("add_split_reference", (ref.get("title"), ref.get("authors"), ref.get("type"), ref.get("periodical"), ref.get("publishedTime"), ref.get("order"), ref.get("language"), 0))
            reference_id = result[-1]
            cursor.callproc("add_paperrefer", (paper_id, reference_id))
        # 存论文参数
        content_count = info.get("wordCount")
        cursor.callproc("add_para",
                            (content_count, template_id, paper_id))
        #存抽取内容
        summary = extract_main_content(text)
        cursor.callproc("add_extract_content", (summary, paper_id))
        #存选题意义分数
        significanceOfTitle = score_paper_title(info.get("title"))
        score1 = significanceOfTitle.get("价值导向")
        suggest1 = significanceOfTitle.get("suggest1")
        score2 = significanceOfTitle.get("选题目的与意义")
        suggest2 = significanceOfTitle.get("suggest2")
        cursor.callproc("add_score", (paper_id, template_id, 2, score1, suggest1))
        cursor.callproc("add_score", (paper_id, template_id, 3, score2, suggest2))
        #获取模板参数
        word_count2, chapter_count2, ref_count2, five_year2, three_year2, english_refer2 = get_templatePara(template_id)
        #获取论文参数
        word_count1, chapter_count1, ref_count1, five_year1, three_year1, english_refer1 = get_paperPara(paper_id)
        #存工作量分数
        wordload = score_workload(word_count1, word_count2, chapter_count1, chapter_count2, ref_count1, ref_count2)
        score3 = wordload.get("工作量")
        suggest3 = wordload.get("suggest")
        cursor.callproc("add_score", (paper_id, template_id, 6, score3, suggest3))
        #存内容组织分数
        cursor.callproc("select_templateChapter", (template_id,))
        for result in cursor.stored_results():
            rows = result.fetchall()
            template_chapter.extend(rows)
        contentOrganization = score_contentOrganization(paper_chapter, template_chapter)
        score4 = contentOrganization.get("内容组织能力")
        suggest4 = contentOrganization.get("suggest")
        cursor.callproc("add_score", (paper_id, template_id, 8, score4, suggest4))
        #存逻辑结构分数
        logicStructure = score_logicStructure(info.get("title"), info.get("abstractCh"), info.get("keywordsCh"), summary)
        score5 = logicStructure.get("逻辑结构")
        suggest5 = logicStructure.get("suggest")
        cursor.callproc("add_score", (paper_id, template_id, 9, score5, suggest5))
        #存文献调研分数
        refSurvey = score_refSurvey(five_year1, five_year2, three_year1, three_year2, english_refer1, english_refer2)
        score6 = refSurvey.get("文献调研能力")
        suggest6 = refSurvey.get("suggest")
        cursor.callproc("add_score", (paper_id, template_id, 11, score6, suggest6))

        othersScore = score_others(text)
        #存写作形式分数
        score7 = othersScore.get("写作形式")
        suggest7 = othersScore.get("写作形式依据")
        cursor.callproc("add_score", (paper_id, template_id, 5, score7, suggest7))
        #存综合应用知识能力分数
        score8 = othersScore.get("综合应用知识能力")
        suggest8 = othersScore.get("综合应用知识能力依据")
        cursor.callproc("add_score",
                        (paper_id, template_id, 12, score8, suggest8))
        #存分析解决问题能力分数
        score9 = othersScore.get("分析解决问题能力")
        suggest9 = othersScore.get("分析解决问题能力依据")
        cursor.callproc("add_score",
                        (paper_id, template_id, 13, score9, suggest9))
        #存创新能力分数
        score10 = othersScore.get("创新能力")
        suggest10 = othersScore.get("创新能力依据")
        cursor.callproc("add_score",
                        (paper_id, template_id, 14, score10, suggest10))
        #存行文规范分数
        score11 = othersScore.get("行文规范")
        suggest11 = othersScore.get("行文规范依据")
        cursor.callproc("add_score",
                        (paper_id, template_id, 16, score11, suggest11))
        #存引用规范分数
        score12 = othersScore.get("引用规范")
        suggest12 = othersScore.get("引用规范依据")
        cursor.callproc("add_score",
                        (paper_id, template_id, 17, score12, suggest12))
        #存选题意义分数
        cursor.callproc("add_firstfactorscore",
                        (paper_id, template_id, "2,3", 1))
        # 存写作安排分数
        cursor.callproc("add_firstfactorscore",
                        (paper_id, template_id, "5,6", 4))
        # 存逻辑构建分数
        cursor.callproc("add_firstfactorscore",
                        (paper_id, template_id, "8,9", 7))
        # 存专业能力分数
        cursor.callproc("add_firstfactorscore",
                        (paper_id, template_id, "11,12,13,14", 10))
        # 存学术规范分数
        cursor.callproc("add_firstfactorscore",
                        (paper_id, template_id, "16,17", 15))
        # 存总分
        cursor.callproc("add_totalscore",
                        (paper_id, template_id, "1,4,7,10,15"))
        conn.commit()
        print(f"论文数据保存成功: paper_id={paper_id}")
        return paper_id
    except Error as e:
        if conn:
            conn.rollback()
        print("保存论文失败:", e)
        raise e
    finally:
        if cursor:
            cursor.close()
        if conn:
            conn.close()